package cn.pugle.ormtest.hibernate.repository;

import cn.pugle.ormtest.hibernate.dto.CustomerDetail;
import cn.pugle.ormtest.hibernate.model.CustomerModel3;
import cn.pugle.ormtest.hibernate.model.OrderModel3;
import cn.pugle.ormtest.hibernate.repository.mixin.MyBaseRepository;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.LongType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author tzp
 * @date 2017/12/26
 */
public interface CustomerRepository extends JpaRepository<CustomerModel3, Long>, MyBaseRepository {
    /**
     * no need to implement. spring auto proxy this method.
     */
    List<CustomerModel3> findByNameLike(String name);

    /**
     * 测试join结果和注解写hql/jpql
     */
    @Query(value = "SELECT new cn.pugle.ormtest.hibernate.dto.CustomerDetail (c.id, c.name, count(o.id)) " +
            "FROM CustomerModel3 c left join c.orders o")
    List<CustomerDetail> customerDetail();

//    /**
//     * sql to dto. one-to-many
//     */
//    default List<CustomerDetail> customerDetail1() {
//        //hibernate支持结果映射, 也比较烦
//        Session session = getEntityManager().unwrap(Session.class);//需要开启事务
//        List orders = session
//                //createNativeQuery(String var1, Class<R> var2);
//                .createNativeQuery("select c.*, count(o) as orderCount " +
//                        " from st_customer3 c left join st_orders o on o.customer_id = c.id " +
//                        " group by c.id")
//                .addEntity("c", CustomerModel3.class)
//                .addJoin("o", "c.orders")
//                .getResultList();
//    }
}
